<!doctype html>



  


<html class="theme-next muse use-motion" lang="zh-Hans">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>



<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />












  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  

  

  

  

  
    
    
    <link href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.1.0" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="MySQL," />








  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.0" />






<meta name="description" content="声明
本文整理自好友分享文档。
原作者：王绍晖个人简介：腾云天下实习DBA">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL备份与恢复">
<meta property="og:url" content="/2016/11/15/MySQL备份与恢复/index.html">
<meta property="og:site_name" content="Porson's Bolg">
<meta property="og:description" content="声明
本文整理自好友分享文档。
原作者：王绍晖个人简介：腾云天下实习DBA">
<meta property="og:updated_time" content="2017-02-23T03:31:11.750Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL备份与恢复">
<meta name="twitter:description" content="声明
本文整理自好友分享文档。
原作者：王绍晖个人简介：腾云天下实习DBA">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Muse',
    sidebar: {"position":"right","display":"post"},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: 'porson',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="/2016/11/15/MySQL备份与恢复/"/>





  <title> MySQL备份与恢复 | Porson's Bolg </title>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  










  
  
    
  

  <div class="container one-collumn sidebar-position-right page-post-detail ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-meta custom-logo">
  
    <div class="site-meta-headline">
      <a>
        <img class="custom-logo-image" src="/images/favicon.png"
             alt="Porson's Bolg"/>
      </a>
    </div>
  

  <div class="custom-logo-site-title">
    <a href="/"  class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <span class="site-title">Porson's Bolg</span>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>
    
      <p class="site-subtitle">MySQL/Python/Golang/Others</p>
    
</div>

<div class="site-nav-toggle">
  <button>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
  </button>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user-md"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">
  <link itemprop="mainEntityOfPage" href="/2016/11/15/MySQL备份与恢复/">

  <span style="display:none" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <meta itemprop="name" content="Porson">
    <meta itemprop="description" content="">
    <meta itemprop="image" content="/images/avatar.gif">
  </span>

  <span style="display:none" itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
    <meta itemprop="name" content="Porson's Bolg">
    <span style="display:none" itemprop="logo" itemscope itemtype="http://schema.org/ImageObject">
      <img style="display:none;" itemprop="url image" alt="Porson's Bolg" src="/images/favicon.png">
    </span>
  </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                MySQL备份与恢复
              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="Post created" itemprop="dateCreated datePublished" datetime="2016-11-15T19:17:06+08:00">
                2016-11-15
              </time>
            

            

            
          </span>

          

          
            
              <span class="post-comments-count">
                <span class="post-meta-divider">|</span>
                <span class="post-meta-item-icon">
                  <i class="fa fa-comment-o"></i>
                </span>
                <a href="/2016/11/15/MySQL备份与恢复/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="2016/11/15/MySQL备份与恢复/" itemprop="commentCount"></span>
                </a>
              </span>
            
          

          

          
          

          

          

        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        <h2 id="声明"><a href="#声明" class="headerlink" title="声明"></a>声明</h2><blockquote>
<p>本文整理自好友分享文档。</p>
<p>原作者：王绍晖<br>个人简介：腾云天下实习DBA</p>
</blockquote>
<a id="more"></a>
<h2 id="binlog的解读"><a href="#binlog的解读" class="headerlink" title="binlog的解读"></a>binlog的解读</h2><ul>
<li>查看binlog：<ul>
<li>mysqlbinlog -vv mysqlserver.00000[1-9]      #可以带正则表达式<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line"># at 3871734  	当前位置</div><div class="line">#160828 10:08:13 server id 2  end_log_pos 3871891  CRC32 0x796512ad</div><div class="line">时间点                服务器id           结束位置    校验码</div><div class="line">Query	thread_id=21	exec_time=0	error_code=0</div><div class="line">        线程ID                           =0代表执行成功</div></pre></td></tr></table></figure>
</li>
</ul>
</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@localhost data]# mysqlbinlog --help</div><div class="line"> --start-position=#		开始位置</div><div class="line"> --stop-position=# 		停止位置</div></pre></td></tr></table></figure>
<h2 id="binlog自动切换的条件："><a href="#binlog自动切换的条件：" class="headerlink" title="binlog自动切换的条件："></a>binlog自动切换的条件：</h2><ul>
<li>达到阀值</li>
<li>数据库重启会出现切换的情况</li>
<li><p>binlog强行切换：</p>
<p><code>mysql&gt; flush logs;</code>    </p>
</li>
</ul>
<blockquote>
<p>查看在使用的哪个日志，可以ls -t看时间</p>
</blockquote>
<h2 id="关于日志的截取："><a href="#关于日志的截取：" class="headerlink" title="关于日志的截取："></a>关于日志的截取：</h2><ul>
<li>binlog的恢复：<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@localhost data]# mysqlbinlog  --start-position=100  --stop-position= 8000 mysqlserver.00001[2-9]	&gt;a.a</div><div class="line">[root@localhost data]# cat a.a | mysql</div></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="逻辑备份和物理备份"><a href="#逻辑备份和物理备份" class="headerlink" title="逻辑备份和物理备份"></a>逻辑备份和物理备份</h2><ul>
<li>逻辑备份：将数据备份成一个文本文件（抽取数据行）</li>
</ul>
<ol>
<li>数据库必须是打开的状态</li>
<li>登陆数据库，将数据抽出来，导到一个文件里</li>
</ol>
<ul>
<li>逻辑备份：mysqldump<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">Usage: mysqldump [OPTIONS] database [tables]		某些表</div><div class="line">OR   mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]	  某些库</div><div class="line">OR   mysqldump [OPTIONS] --all-databases [OPTIONS]	备份所有的库</div></pre></td></tr></table></figure>
</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div></pre></td><td class="code"><pre><div class="line">options：	</div><div class="line">-u </div><div class="line">-p</div><div class="line">-F：flush log  		#恢复的时候直接从下一个日志开始就可以了</div><div class="line">-l：lock tables  	#数据的一致性，在备份的期间，myisam表是不能用的</div><div class="line">--single-transaction 		#影响innodb表的，不锁表和行</div><div class="line">-fields-terminated-by=name 		#列和列之间的分隔符</div><div class="line">                      Fields in the output file are terminated by the given  string.</div><div class="line">--fields-enclosed-by=name </div><div class="line">                      Fields in the output file are enclosed by the given character.</div><div class="line">--fields-optionally-enclosed-by=name </div><div class="line">                      Fields in the output file are optionally enclosed by the given character.</div><div class="line">--fields-escaped-by=name </div><div class="line">                      Fields in the output file are escaped by the given</div><div class="line"></div><div class="line">-T, --tab=name      Create tab-separated textfile for each table to given	#指定tab键为分隔符</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">[root@localhost tmp]# mysqldump -uroot -p -l -F  --single-transaction -T /tmp -S /usr/local/mysql/data/mysql.sock test t1 &gt;t1.txt	#文件名必须与表的名字一致，否则没有内容；-T指定那个文件夹，这条命令就要到哪个文件夹下面执行</div><div class="line">[root@localhost tmp]# cat t1.txt </div><div class="line">1	a</div><div class="line">2	b</div><div class="line">3	c</div><div class="line">4	e</div></pre></td></tr></table></figure>
<ul>
<li><p>备份一张表：<br><code>mysqldump -uroot -p -l -F --single-transaction -S /usr/local/mysql/data/mysql.sock test t1 &gt;aa.sql</code></p>
</li>
<li><p>恢复一张表：<br><code>cat aa.sql | mysql -uroot -p123123 -S /usr/local/mysql/data/mysql.sock test</code></p>
</li>
</ul>
<h3 id="关注的点："><a href="#关注的点：" class="headerlink" title="关注的点："></a>关注的点：</h3><blockquote>
<p>备份出来的数据对应的binlog的日志起点<br>备份时候要把表给锁住，对myisam来说，备份期间锁住的表是不能用的；对innodb来说，不锁表，在备份期间结合MVCC和undo把开始备份时刻和备份完成时刻之间的数据恢复</p>
</blockquote>
<h2 id="物理备份—xtrabackup"><a href="#物理备份—xtrabackup" class="headerlink" title="物理备份—xtrabackup"></a>物理备份—xtrabackup</h2><p>xtrabackup既可以备份myisam表也可以备份innodb表<br>物理备份：可以认为是cp，备份的是数据文件页和里面的数据页</p>
<ul>
<li>冷备：数据库关闭，把数据库相关的文件cp出来</li>
<li>热备：数据库开启，使用相关的工具将数据备份出来</li>
</ul>
<blockquote>
<p>不管物理或逻辑，必须知道你备份的点，能跟binlog连起来的，备份恢复的时候，binlog 的起点必须是你备份的那个点</p>
</blockquote>
<ol>
<li>建立一个备份目录，注意：属主属组一定要是mysql</li>
<li>备份数据库：将相关的数据文件拷贝出来</li>
</ol>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@localhost ~]# innobackupex --user=root --password=123123  --socket=/usr/local/mysql/data/mysql.sock --port 3307 --no-timestamp  /backup/		#不建立一个时间戳的目录</div><div class="line">这个命令默认会去找rpm安装的mysql，最好把rpm安装的mysql配置文件名字改一下</div><div class="line">成功的话在最后会有一个complete</div></pre></td></tr></table></figure>
<ul>
<li>backup里面相关文件：<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">[root@localhost ~]# ll /backup/</div><div class="line">total 77860</div><div class="line">-rw-r-----. 1 root root      387 Sep  8 17:22 backup-my.cnf</div><div class="line">-rw-r-----. 1 root root 79691776 Sep  8 17:21 ibdata1</div><div class="line">drwx------. 2 root root     4096 Sep  8 17:22 mysql</div><div class="line">drwx------. 2 root root     4096 Sep  8 17:22 performance_schema</div><div class="line">drwx------. 2 root root     4096 Sep  8 17:22 test</div><div class="line">drwx------. 2 root root     4096 Sep  8 17:22 tpcc1000</div><div class="line">-rw-r-----. 1 root root       23 Sep  8 17:22 xtrabackup_binlog_info</div><div class="line">-rw-r-----. 1 root root      119 Sep  8 17:22 xtrabackup_checkpoints</div><div class="line">-rw-r-----. 1 root root      538 Sep  8 17:22 xtrabackup_info</div><div class="line">-rw-r-----. 1 root root     2560 Sep  8 17:22 xtrabackup_logfile</div></pre></td></tr></table></figure>
</li>
</ul>
<blockquote>
<p>xtrabackup_logfile：在备份期间产生的redo log，从log file里面取出来，放到这个文件里。<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">xtrabackup_info：</div><div class="line">start_time = 2016-09-08 17:21:16</div><div class="line">end_time = 2016-09-08 17:22:37</div><div class="line">lock_time = 0</div><div class="line">binlog_pos = filename &apos;mysqlserver.000023&apos;, position &apos;120&apos;	#binlog位置，从哪里开始备</div><div class="line">innodb_from_lsn = 0	#每一个数据页都有一个lsn号，这两行表示全备（方便增备）</div><div class="line">innodb_to_lsn = 3171620346</div><div class="line">xtrabackup_binlog_info：mysqlserver.000023	120</div><div class="line">xtrabackup_checkpoints：backup_type = full-backuped	#代表全备</div></pre></td></tr></table></figure></p>
</blockquote>
<ul>
<li><p>模拟环境：删除一张表</p>
<ul>
<li>apply-log<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">[root@localhost backup]# innobackupex --apply-log /backup/		#apply-log带着一个隐藏的rollback，如果不想roll back加上--redo-only</div><div class="line">！！！一定要看到一个complete</div><div class="line">[root@localhost backup]# cat xtrabackup_checkpoints</div><div class="line">backup_type = full-prepared		#不仅是全备而且apply log了</div></pre></td></tr></table></figure>
</li>
</ul>
</li>
<li><p>关闭数据库，删除数据库（注意：一定不要删除binlog）<br>将binlog从里面移出来，注意不要放到backup下面，否则一会恢复的时候binlog页都给恢复了，这个是不需要的。</p>
</li>
</ul>
<ul>
<li><p>恢复数据库：就是把backup下面的文件再拷贝回去</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost backup]# innobackupex --defaults-file=/etc/mysla.cnf  --copy-back /backup/	#注意：最好指定配置文件，有的会报如下错误</div></pre></td></tr></table></figure>
</li>
<li><p>文件拷贝完成记得一定要修改datadir下面文件的属主和属组，重启数据库</p>
</li>
<li><p>跑binlog：<br>起始位置以及哪个binlog：xtrabackup_info 记录在这个文件里<br>终点位置需要我们自己去找：mysqlbinlog -vv mysqlserver.000023 | grep -i “drop” -C 100        </p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@localhost binlog]# cat a.sql | mysql -uroot -p -S /usr/local/mysql/data/mysql.sock </div><div class="line">Enter password:</div></pre></td></tr></table></figure>
</li>
<li><p>跑binlog的时候去监控：show processlist—是实时变化的</p>
</li>
</ul>
<h2 id="mysql启动流程分析："><a href="#mysql启动流程分析：" class="headerlink" title="mysql启动流程分析："></a>mysql启动流程分析：</h2><ol>
<li>cat /etc/my.cnf       文件是否存在，文件内容是否正确，主要看datadir</li>
<li>看datadir的权限</li>
<li>进入datadir，看里面的内容是否齐全：ibdata1，ib_logfile，mysql，performation_scheme,information_scheme</li>
<li>确认一下errorlog，pid文件，sock文件，</li>
<li>mysql_safe –defaults-file=/etc/my.cnf  &amp;</li>
<li>ps -ef | grep mysqld<ol>
<li>tail -100f errorlog</li>
</ol>
</li>
<li>登陆mysql -uroot -p -h127.0.0.1 或者mysql -uroot -p -S /…/…sock</li>
</ol>
<h2 id="备份方案："><a href="#备份方案：" class="headerlink" title="备份方案："></a>备份方案：</h2><ol>
<li>备份期间会产生大量的IO，qps和tps都会大幅度的下降，从某种意义来说，在备份期间，数据库基本没法用</li>
<li>将备份的文件传走(传到从库)：对IO和网络带宽产生负载</li>
<li><p>恢复的时间：恢复的时候，binlog跑得特别慢，生产做业务的时候是并发执行的，恢复的时候只有一个线程在跑</p>
<ul>
<li>需要考虑的点：</li>
</ul>
</li>
<li>备份对生产的影响,备份和数据传输</li>
<li>备份的恢复时间</li>
</ol>
<h3 id="对于恢复的时间的计算"><a href="#对于恢复的时间的计算" class="headerlink" title="对于恢复的时间的计算"></a>对于恢复的时间的计算</h3><blockquote>
<p>恢复时间=备份拷贝回来的时间（IO和网络带宽）+跑binlog的时间（不太容易计算，需要实际测试）</p>
<h3 id="注意"><a href="#注意" class="headerlink" title="注意"></a>注意</h3><ul>
<li>binlog的恢复时间非常不可控，binlog中有的sql语句特别慢，甚至跑的时候跑不动</li>
</ul>
</blockquote>
<h2 id="增量备份"><a href="#增量备份" class="headerlink" title="增量备份"></a>增量备份</h2><p>主要是对innodb而言的，对于myisam和其他的表还是一个全拷贝。<br>每个数据页都会有一个lsn号，每产生一次变化，lsn都会发生改变。</p>
<ul>
<li>增量备份<ul>
<li>备份的是发生变化的页（这些数据页指的是LSN大于xtrabackup_checkpoints中给定的LSN），增备是基于全备的，第一次的增备的数据必须基于上一次的全备。</li>
</ul>
</li>
<li>增量备份的过程：<ul>
<li>缺点：要把所有的页都扫描一遍才能知道哪些页发生变化，所以备份的时候对库的压力并没有变小，只是备份出来的数据量有可能变少了</li>
<li>好处：恢复的时候是可控的，可以把增量apply到全备里面；备份完成之后就可以apply到全备里面去，不是非要等到恢复的时候再apply。</li>
</ul>
</li>
<li>模拟完全+增量+prepare</li>
</ul>
<ol>
<li>完全备份,备份完记得apply log</li>
<li>建立一个增备的文件并修改权限</li>
</ol>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@localhost backup]# mkdir /back_inr</div><div class="line">[root@localhost backup]# chown -R mysql:mysql /back_inr/</div></pre></td></tr></table></figure>
<ul>
<li><p>第一次增量备份（增量备份还是希望有时间戳的）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost ~]# innobackupex --user=root --password=123123 --socket=/usr/local/mysql/data/mysql.sock --incremental /back_inr/ --incremental-basedir=/backup/</div></pre></td></tr></table></figure>
</li>
<li><p>第二次增量备份（和第一次执行的命令大致相同，只有他的–incremental-basedir应该指向上一次的增量备份所在的目录）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost back_inr]# innobackupex --user=root --password=123123 --socket=/usr/local/mysql/data/mysql.sock --incremental /back_inr/ --incremental-basedir=/back_inr/2016-09-09_08-50-13/</div></pre></td></tr></table></figure>
</li>
<li><p>把增量prepare到全备里面去,只要有增量就要一直redo-only</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost ~]# innobackupex --socket=/usr/local/mysql/data/mysql.sock   --user=root --password=123123 --apply-log-only --redo-only  /backup/ --incremental-dir=/back_inr/2016-09-09_08-50-13/</div></pre></td></tr></table></figure>
</li>
<li><p>跑binlog<br>起点在xtrbackup_binlog_info里面找</p>
</li>
</ul>
<h3 id="copy-back和move-back的区别："><a href="#copy-back和move-back的区别：" class="headerlink" title="copy-back和move-back的区别："></a>copy-back和move-back的区别：</h3><p>如果备份和数据库所在的是一个文件系统，move-back的时候其实就是给了一个名字，速度非常的快</p>
<h2 id="innobackupex参数详解："><a href="#innobackupex参数详解：" class="headerlink" title="innobackupex参数详解："></a>innobackupex参数详解：</h2><p>[–compress]和[–compress-threads=NUMBER-OF-THREADS]：备份的时候压缩非常消耗CPU，压的时候可以加大cpu的力度<br>[–encrypt=ENCRYPTION-ALGORITHM] ：备份有加密的需求<br>–no-timestamp：备份完之后不生成时间戳<br>–compact：优化，所有表上的二级索引不备份</p>
<p>–parallel=NUMBER-OF-FORKS：只支持全备，备份的时候可以用并发（并行）的方式去备份（在带宽足够的条件下，可以大量的减少备份的时间）<br>–throttle=#  阀值，<br>限流：限制流量，控制他的IO使用量，降低对主库使用的影响，不要对生产产生影响<br>–safe-slave-backup：停止对从库的更新，然后进行备份，这样备份非常快。<br>–log-copy-interval=# ：每隔多长时间记录一下日志<br>–kill-long-queries-timeout=#：如果系统里有一个长事务一直未提交，加锁加不上，那么备份的时候就会hang住，等多长时间事务不提交加不上锁的话就把他杀死<br>–ftwrl-wait-timeout=#<br>–no-lock：在备份期间不加锁<br>使用这个参数要保证在备份期间：<br>没有DDL（会导致数据不一致）<br>没有对myisam表的更新<br>–redo-only  ：This is necessary if the backup  will have incremental changes applied to it later. 只要后面还有增量就一定要加上redo-only</p>
<ul>
<li>log scan up to：<br>在备份的过程中会有大量的log scan up to：不断的记日志，数据页不断的更新；说明我读到的这个数据页在不断的更新。</li>
</ul>
<h2 id="！！！注意"><a href="#！！！注意" class="headerlink" title="！！！注意"></a>！！！注意</h2><ul>
<li>备份期间innodb表也会短时间的加锁，最好在从库上备份，并且暂停主库对从库的更新</li>
</ul>
<h2 id="表的导入导出"><a href="#表的导入导出" class="headerlink" title="表的导入导出"></a>表的导入导出</h2><ul>
<li>一般都是基于表的。<h3 id="导出：两种方式"><a href="#导出：两种方式" class="headerlink" title="导出：两种方式"></a>导出：两种方式</h3></li>
<li>1.select … into outfile  options<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">options：</div><div class="line"></div><div class="line">mysql&gt; select * from t3 into outfile &quot;/data/1.txt&quot; fields terminated by &apos;,&apos;;	#注意权限的问题</div><div class="line"></div><div class="line">mysql&gt; select * from t3 into outfile &quot;/data/1.txt&quot; fields terminated by &apos;,&apos; lines terminated by &apos;..........&apos;;</div><div class="line"></div><div class="line">mysql&gt; select * from t3 into outfile &quot;/data/1.txt&quot; fields terminated by &apos;,&apos;  enclosed by &apos;&quot;&apos;;</div><div class="line"></div><div class="line">mysql&gt; select * from t3 into outfile &quot;/data/1.txt&quot; fields terminated by &apos;,&apos;  optionally  enclosed by &apos;&quot;</div></pre></td></tr></table></figure>
</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; insert into t4 values(1,&apos;\tdasd&apos;);		#插入一行数据以tab分隔</div><div class="line">mysql&gt; select * from t4 into outfile &quot;/data/1.txt&quot; fields terminated by &apos;,&apos;  escaped by &apos;\\&apos;;&apos;;		#只在个别的字符类型上加引用符</div></pre></td></tr></table></figure>
<ul>
<li>2.mysqldump -T<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump -u username- T  target_ dir dbname  tablename  [option]</div></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="导入：load-data"><a href="#导入：load-data" class="headerlink" title="导入：load data"></a>导入：load data</h3><ul>
<li>load data<br>load比insert into速度要快好多，批量导入<br>mysql &gt;LOAD DATA  [LOCAL]  INFILE  ‘filename’  INTO  TABLE tablename [option] </li>
<li>mysqlimport<br>Shell&gt; mysqlimport -u root  -p<em>*</em>  [–LOCAL]  dbname order_tab.txt  [option] </li>
</ul>

      
    </div>

    <div>
      
        

      
    </div>

    <div>
      
        

      
    </div>


    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/MySQL/" rel="tag"># MySQL</a>
          
        </div>
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2017/02/03/hello-world/" rel="prev" title="Hello World">
                Hello World <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </article>



    <div class="post-spread">
      
    </div>
  </div>

          
          </div>
          


          
  <div class="comments" id="comments">
    
      <div class="ds-thread" data-thread-key="2016/11/15/MySQL备份与恢复/"
           data-title="MySQL备份与恢复" data-url="/2016/11/15/MySQL备份与恢复/">
      </div>
    
  </div>


        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap" >
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/images/avatar.gif"
               alt="Porson" />
          <p class="site-author-name" itemprop="name">Porson</p>
          <p class="site-description motion-element" itemprop="description">DBA/Python程序员/开源爱好者</p>
        </div>
        <nav class="site-state motion-element">
        
          
            <div class="site-state-item site-state-posts">
              <a href="/archives">
                <span class="site-state-item-count">14</span>
                <span class="site-state-item-name">日志</span>
              </a>
            </div>
          

          

          
            <div class="site-state-item site-state-tags">
              <a href="/tags">
                <span class="site-state-item-count">10</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        

        <div class="links-of-author motion-element">
          
            
              <span class="links-of-author-item">
                <a href="https://github.com/porson" target="_blank" title="GitHub">
                  
                    <i class="fa fa-fw fa-github"></i>
                  
                  GitHub
                </a>
              </span>
            
          
        </div>

        
        
          <div class="cc-license motion-element" itemprop="license">
            <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" class="cc-opacity" target="_blank">
              <img src="/images/cc-by-nc-sa.svg" alt="Creative Commons" />
            </a>
          </div>
        

        
        

        


      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#声明"><span class="nav-number">1.</span> <span class="nav-text">声明</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#binlog的解读"><span class="nav-number">2.</span> <span class="nav-text">binlog的解读</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#binlog自动切换的条件："><span class="nav-number">3.</span> <span class="nav-text">binlog自动切换的条件：</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#关于日志的截取："><span class="nav-number">4.</span> <span class="nav-text">关于日志的截取：</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#逻辑备份和物理备份"><span class="nav-number">5.</span> <span class="nav-text">逻辑备份和物理备份</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#关注的点："><span class="nav-number">5.1.</span> <span class="nav-text">关注的点：</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#物理备份—xtrabackup"><span class="nav-number">6.</span> <span class="nav-text">物理备份—xtrabackup</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#mysql启动流程分析："><span class="nav-number">7.</span> <span class="nav-text">mysql启动流程分析：</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#备份方案："><span class="nav-number">8.</span> <span class="nav-text">备份方案：</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#对于恢复的时间的计算"><span class="nav-number">8.1.</span> <span class="nav-text">对于恢复的时间的计算</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#注意"><span class="nav-number">8.2.</span> <span class="nav-text">注意</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#增量备份"><span class="nav-number">9.</span> <span class="nav-text">增量备份</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#copy-back和move-back的区别："><span class="nav-number">9.1.</span> <span class="nav-text">copy-back和move-back的区别：</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#innobackupex参数详解："><span class="nav-number">10.</span> <span class="nav-text">innobackupex参数详解：</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#！！！注意"><span class="nav-number">11.</span> <span class="nav-text">！！！注意</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#表的导入导出"><span class="nav-number">12.</span> <span class="nav-text">表的导入导出</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#导出：两种方式"><span class="nav-number">12.1.</span> <span class="nav-text">导出：两种方式</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#导入：load-data"><span class="nav-number">12.2.</span> <span class="nav-text">导入：load data</span></a></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  <span class="author" itemprop="copyrightHolder">Create by Porson </span>
    <span class="with-love">
    <i class="fa fa-copyright"></i>
  </span>
   2016 - 
  <span itemprop="copyrightYear">2017</span>
  All copyright

</div>



        

        
      </div>
    </footer>

    <div class="back-to-top">
      <i class="fa fa-arrow-up"></i>
    </div>
  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  




  
  <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>

  
  <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  
  <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  
  <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.0"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.0"></script>



  
  

  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.0"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.0"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.0"></script>



  

  
    
  

  <script type="text/javascript">
    var duoshuoQuery = {short_name:"lpx"};
    (function() {
      var ds = document.createElement('script');
      ds.type = 'text/javascript';ds.async = true;
      ds.id = 'duoshuo-script';
      ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
      ds.charset = 'UTF-8';
      (document.getElementsByTagName('head')[0]
      || document.getElementsByTagName('body')[0]).appendChild(ds);
    })();
  </script>

  
    
    
    <script src="/lib/ua-parser-js/dist/ua-parser.min.js?v=0.7.9"></script>
    <script src="/js/src/hook-duoshuo.js"></script>
  












  
  

  
  
    <script type="text/x-mathjax-config">
      MathJax.Hub.Config({
        tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
        }
      });
    </script>

    <script type="text/x-mathjax-config">
      MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for (i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
        }
      });
    </script>
    <script type="text/javascript" src="//cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
  


  

  

  


</body>
</html>
